package cn.itcast.web.controller.cargo;

import cn.itcast.service.cargo.ContractService;
import cn.itcast.vo.ContractProductVo;
import cn.itcast.web.controller.BaseController;
import com.alibaba.dubbo.config.annotation.Reference;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.ServletOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

@Controller
@RequestMapping("/cargo/contract")
@Slf4j
public class OutProductController extends BaseController {

    @Reference
    private ContractService contractService;

    //1. 进入导出页面
    @RequestMapping("/print")
    public String print() {
        return "cargo/print/contract-print";
    }

    //2. 导出出货表 A 普通导出
    @RequestMapping("/printExcel_11")
    @ResponseBody
    public void printExcel_11(String inputDate) throws IOException {
        //第一步：导出第一行（大标题）
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 合并单元格(开始行0 结束行0 开始列1 结束列8)
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
        // 设置列宽
        sheet.setColumnWidth(0, 256 * 6);
        sheet.setColumnWidth(1, 256 * 26);
        sheet.setColumnWidth(2, 256 * 11);
        sheet.setColumnWidth(3, 256 * 29);
        sheet.setColumnWidth(4, 256 * 12);
        sheet.setColumnWidth(5, 256 * 15);
        sheet.setColumnWidth(6, 256 * 10);
        sheet.setColumnWidth(7, 256 * 10);
        sheet.setColumnWidth(8, 256 * 10);

        Row row = sheet.createRow(0);
        row.setHeightInPoints(36);
        Cell cell = row.createCell(1);
        // 设置单元格内容： inputDate = 2019-06
        String title = inputDate.replace("-0", "-").replace("-", "年") + "月份出货表";
        cell.setCellValue(title);
        // 设置样式(水平垂直居中、加粗)
        cell.setCellStyle(this.bigTitle(workbook));

        //第二步：导出第二行（表格的表头）
        String[] titles = {"客户", "订单号", "货号", "数量", "工厂", "工厂交期", "船期", "贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(this.title(workbook));
        }

        //第三步：导出数据行
        List<ContractProductVo> list = contractService.findByShipTime(inputDate + "%");
        if (list != null && list.size() > 0) {
            int index = 2;
            for (ContractProductVo vo : list) {
                // 创建行，从第3行开始...
                row = sheet.createRow(index++);
                row.setHeightInPoints(24);

                /* 注意：导出往excel中的单元格写入数据时候，不能写入null值*/
                cell = row.createCell(1);
                cell.setCellValue(vo.getCustomName());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(2);
                cell.setCellValue(vo.getContractNo());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(3);
                cell.setCellValue(vo.getProductNo());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(4);
                cell.setCellValue(vo.getCnumber() != null ? vo.getCnumber() : 0);
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(5);
                cell.setCellValue(vo.getFactoryName());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(6);
                cell.setCellValue(vo.getDeliveryPeriod());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(7);
                cell.setCellValue(vo.getShipTime());
                cell.setCellStyle(this.text(workbook));

                cell = row.createCell(8);
                cell.setCellValue(vo.getTradeTerms());
                cell.setCellStyle(this.text(workbook));
            }
        }

        //第四步：导出下载
        response.setHeader("content-disposition", "attachment;fileName=export.xlsx");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        out.close();
    }


    //3. 导出出货表 B 模板导出，先读取一个excel模板，往其中设置数据
    @RequestMapping("/printExcel_22")
    @ResponseBody
    public void printExcel_22(String inputDate) throws IOException {
        //【读取excel模板】
        InputStream in =
                session.getServletContext()
                        .getResourceAsStream("/make/xlsprint/tOUTPRODUCT.xlsx");
        //【根据excel模板文件流，创建工作簿】
        Workbook workbook = new XSSFWorkbook(in);
        //【获取工作表】
        Sheet sheet = workbook.getSheetAt(0);

        //【设置第一行第二列数据】
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(1);
        String title = inputDate.replace("-0", "-").replace("-", "年") + "月份出货表";
        cell.setCellValue(title);

        //【获取第三行样式】
        row = sheet.getRow(2);
        CellStyle[] cellStyles = new CellStyle[8];
        for (int i = 0; i < cellStyles.length; i++) {
            cellStyles[i] = row.getCell(i + 1).getCellStyle();
        }

        //第三步：导出数据行
        List<ContractProductVo> list = contractService.findByShipTime(inputDate + "%");
        if (list != null && list.size() > 0) {
            int index = 2;
            for (ContractProductVo vo : list) {
                // 创建行，从第3行开始...
                row = sheet.createRow(index++);

                /* 注意：导出往excel中的单元格写入数据时候，不能写入null值*/
                cell = row.createCell(1);
                cell.setCellValue(vo.getCustomName());
                cell.setCellStyle(cellStyles[0]);

                cell = row.createCell(2);
                cell.setCellValue(vo.getContractNo());
                cell.setCellStyle(cellStyles[1]);

                cell = row.createCell(3);
                cell.setCellValue(vo.getProductNo());
                cell.setCellStyle(cellStyles[2]);

                cell = row.createCell(4);
                cell.setCellValue(vo.getCnumber() != null ? vo.getCnumber() : 0);
                cell.setCellStyle(cellStyles[3]);

                cell = row.createCell(5);
                cell.setCellValue(vo.getFactoryName());
                cell.setCellStyle(cellStyles[4]);

                cell = row.createCell(6);
                cell.setCellValue(vo.getDeliveryPeriod());
                cell.setCellStyle(cellStyles[5]);

                cell = row.createCell(7);
                cell.setCellValue(vo.getShipTime());
                cell.setCellStyle(cellStyles[6]);

                cell = row.createCell(8);
                cell.setCellValue(vo.getTradeTerms());
                cell.setCellStyle(cellStyles[7]);
            }
        }

        //第四步：导出下载
        response.setHeader("content-disposition", "attachment;fileName=export.xlsx");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        out.close();
    }


    //4. 模拟百万数据导出，使用SXSSF，不支持模板导出
    @RequestMapping("/printExcel")
    @ResponseBody
    public void printExcel(String inputDate) throws IOException {
        //第一步：导出第一行（大标题）
        /**
         * XSSF改为SXSSF, 支持百万数据poi的导出
         */
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 合并单元格(开始行0 结束行0 开始列1 结束列8)
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
        // 设置列宽
        sheet.setColumnWidth(0, 256 * 6);
        sheet.setColumnWidth(1, 256 * 26);
        sheet.setColumnWidth(2, 256 * 11);
        sheet.setColumnWidth(3, 256 * 29);
        sheet.setColumnWidth(4, 256 * 12);
        sheet.setColumnWidth(5, 256 * 15);
        sheet.setColumnWidth(6, 256 * 10);
        sheet.setColumnWidth(7, 256 * 10);
        sheet.setColumnWidth(8, 256 * 10);

        Row row = sheet.createRow(0);
        row.setHeightInPoints(36);
        Cell cell = row.createCell(1);
        // 设置单元格内容： inputDate = 2019-06
        String title = inputDate.replace("-0", "-").replace("-", "年") + "月份出货表";
        cell.setCellValue(title);
        // 设置样式(水平垂直居中、加粗)
        cell.setCellStyle(this.bigTitle(workbook));

        //第二步：导出第二行（表格的表头）
        String[] titles = {"客户", "订单号", "货号", "数量", "工厂", "工厂交期", "船期", "贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(this.title(workbook));
        }

        //第三步：导出数据行
        List<ContractProductVo> list = contractService.findByShipTime(inputDate + "%");
        if (list != null && list.size() > 0) {
            int index = 2;
            for (ContractProductVo vo : list) {
                for (int i = 1; i <= 6000; i++) { // 模拟百万数据导出
                    // 创建行，从第3行开始...
                    row = sheet.createRow(index++);
                    row.setHeightInPoints(24);

                    /* 注意：导出往excel中的单元格写入数据时候，不能写入null值*/
                    cell = row.createCell(1);
                    cell.setCellValue(vo.getCustomName());

                    cell = row.createCell(2);
                    cell.setCellValue(vo.getContractNo());

                    cell = row.createCell(3);
                    cell.setCellValue(vo.getProductNo());

                    cell = row.createCell(4);
                    cell.setCellValue(vo.getCnumber() != null ? vo.getCnumber() : 0);

                    cell = row.createCell(5);
                    cell.setCellValue(vo.getFactoryName());

                    cell = row.createCell(6);
                    cell.setCellValue(vo.getDeliveryPeriod());

                    cell = row.createCell(7);
                    cell.setCellValue(vo.getShipTime());

                    cell = row.createCell(8);
                    cell.setCellValue(vo.getTradeTerms());
                }
            }
        }

        //第四步：导出下载
        response.setHeader("content-disposition", "attachment;fileName=export.xlsx");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        out.close();
    }


    //大标题的样式
    public CellStyle bigTitle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);//字体加粗
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);                //横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);        //纵向居中
        return style;
    }

    //小标题的样式
    public CellStyle title(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);                //横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);        //纵向居中
        style.setBorderTop(BorderStyle.THIN);                        //上细线
        style.setBorderBottom(BorderStyle.THIN);                    //下细线
        style.setBorderLeft(BorderStyle.THIN);                        //左细线
        style.setBorderRight(BorderStyle.THIN);                        //右细线
        return style;
    }

    //文字样式
    public CellStyle text(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("Times New Roman");
        font.setFontHeightInPoints((short) 10);

        style.setFont(font);

        style.setAlignment(HorizontalAlignment.LEFT);                //横向居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);        //纵向居中
        style.setBorderTop(BorderStyle.THIN);                        //上细线
        style.setBorderBottom(BorderStyle.THIN);                    //下细线
        style.setBorderLeft(BorderStyle.THIN);                        //左细线
        style.setBorderRight(BorderStyle.THIN);                        //右细线

        return style;
    }
}